package com.googlecode.bip.dao.deprecated;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.naming.NamingException;
import org.apache.log4j.Logger;

import com.googlecode.bip.dao.BipDAO;
import com.googlecode.bip.dao.DBHelper;
import com.googlecode.bip.dao.Listable;
import com.googlecode.bip.dao.LocalDAO;
import com.googlecode.bip.entity.deprecated.Category;

/**
 *
 * @author Claude CHOISNET
 */
@Deprecated
public class CategoryDAO
    extends BipDAO
        implements Listable<Category>
{
    private static final long serialVersionUID = 1L;
    private final static transient Logger slogger = Logger.getLogger( CategoryDAO.class );

    /**
     * Build CategoryDAO
     */
    public CategoryDAO()
    {
    }

//    @Deprecated
//    public CategoryDB( final HttpServletRequest request )
//        throws NamingException
//    {
////        super( BiPolSession.getSession( request ).getDataSource() );
//    }
//
//    @Deprecated
//    public CategoryDB( final DataSource dataSource )
//    {
////        super( dataSource );
//    }

    @Override //Listable<Category>
    public List<Category> getList( String sqlQuery ) throws SQLException, NamingException
    {
        return DBHelper.getList( this, getDataSource(), sqlQuery );
    }

    @Override //Listable<Category>
    public Category newInstance(final ResultSet rSet )
        throws SQLException
    {
        return new Category().set( rSet );
    }

    /**
     * Returns all Category object for an idcategory (all languages)
     * @param idcategory
     * @return all Category object for an idcategory (all languages)
     * @throws SQLException
     * @throws NamingException
     */
    public List<Category> getCategoryByIdList( final int idcategory )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `oldcategoryview` WHERE `idcategory`='"
                + idcategory + "'";

        return getList( sql );
    }

    /**
     * Returns all Category objects for this idlocal (no user values)
     * @param idlocal
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public List<Category> getCategoryByLocalList( final String idlocal )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `oldcategoryview` WHERE `idlocal`='"
                + idlocal + "'";

        return getList( sql );
    }

    /**
     * Returns all Category objects for this idlocal filled when possible
     * with user values.
     *
     * @param idlocal
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public  List<Category> getCategoryByLocalList(
            final String    idlocal,
            final int       iduser,
            final Timestamp userdate
            ) throws SQLException, NamingException
    {
        List<Category> filledList = new ArrayList<Category>();

        for(Category cat:getCategoryByLocalList( idlocal )) {
            Category category = getCategoryForUser( cat, iduser,idlocal,userdate );

            if( category == null ) {
                // No values for user, use default
                filledList.add( cat );
                }
            else {
                filledList.add( category );
                }
            }
        return filledList;
    }


    /**
     * Returns List of all categories (1 by local), return any local
     * @param showDisabled if true show all categories event if disabled,
     *        if false return {@link #getOneCategoryByIdLocalList()}
     * @return List of all categories (1 by local)
     * @throws SQLException
     * @throws NamingException
     */
    public List<Category> getOneCategoryByIdLocalList(boolean showDisabled)
        throws SQLException, NamingException
    {
        if( showDisabled ) {
            return getList( "SELECT * FROM `oldcategoryview` GROUP BY `idcategory`" );
            }
        else {
            return getOneCategoryByIdLocalList();
            }
    }

    /**
     * Returns List of all active categories (1 by local), return any local
     * @return List of all active categories (1 by local)
     * @throws SQLException
     * @throws NamingException
     */
    public List<Category> getOneCategoryByIdLocalList()
        throws SQLException, NamingException
    {
        return getList(
            "SELECT * FROM `oldcategoryview` WHERE `isactive`='1' GROUP BY `idcategory`;"
            );
    }

    /**
     *
     * @param idcategory
     * @param idlocal
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public Category getCategory(
            final int    idcategory,
            final String idlocal
            )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `oldcategoryview` WHERE `idcategory`='"
                + idcategory
                + "' AND `idlocal`='"
                + idlocal
                + "'";

        return DBHelper.get( this, sql );
    }

    /**
     * Get Category and fill user value
     *
     * @param idcategory
     * @param iduser
     * @param idlocal
     * @param userdate
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public Category getCategoryForUser(
            final int       idcategory,
            final int       iduser,
            final String    idlocal,
            final Timestamp userdate
            )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM (SELECT * FROM `olduservaluedetailview`WHERE `idcategory`=' " +
            + idcategory
            + "' AND `iduser`='" +
            + iduser
            + "') Y WHERE Y.`idlocal`='"
            + idlocal
            + "' AND Y.`userdate`='"
            + userdate
            + "'";

        return DBHelper.get( this, sql );
    }

    /**
     * Get Category and fill user value
     *
     * @param category
     * @param iduser
     * @param idlocal
     * @param userdate
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public Category getCategoryForUser(
            final Category  category,
            final int       iduser,
            final String    idlocal,
            final Timestamp userdate
            )
        throws SQLException, NamingException
    {
        return getCategoryForUser(
                    category.getIdcategory(),
                    iduser,
                    idlocal,
                    userdate
                    );
    }

    /**
     *
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public List<Category> getUserCategoryList( final String whereClause )
        throws SQLException, NamingException
    {
        final StringBuilder sb = new StringBuilder();
        sb.append( "select * from `uservaluedetailview`" );

        if( whereClause != null ) {
            sb.append( " where " );
            sb.append( whereClause );
        }

        slogger.info( sb.toString() );

        //return Category.getList( dataSource, sb.toString() );
        return getList( sb.toString() );
    }


    /**
     * @return all existing locale for this Category
     * @throws SQLException
     * @throws NamingException
     */
    public Map<String,Category> getCategoryLocalMap(
            final int idcategory
            )
            throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `oldcategoryview` WHERE `idcategory`="
            + idcategory;

        final List<Category>        cl  = getList( sql );
        final Map<String,Category>  map = new HashMap<String,Category>();

        for(Category c: cl) {
            map.put( c.getIdlocal(), c );
            }

        return map;
    }

    /**
     * @return all locals for this Category, event if no translation
     *         for this Category
     * @throws SQLException
     * @throws NamingException
     */
    public Map<String,Category> getCategoryForAllLocalsMap(
            final int idcategory
            )
            throws SQLException, NamingException
    {
        final Map<String,Category>  map  = getCategoryLocalMap( idcategory );
        //final Map<String, String>   lmap = new  LocalDB( getDataSource() ).getLocalMap();
        final Map<String, String>   lmap = new  LocalDAO().getLocalMap();

        Category defaultCategory = null;

        for( String idlocal:lmap.keySet() ) {
            Category c = map.get( idlocal );

            if( c == null ) {
                if( defaultCategory == null ) {
                    defaultCategory = map.values().iterator().next();
                    }
                Category clone = new Category( defaultCategory );

                clone.setIdlocal( idlocal );
                clone.setName( null );
                clone.setDescription( null );

                map.put( idlocal, clone );
                }
            }

        return map;
    }
}

